﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Dynamic;
using System.Data.SqlClient;
using System.Configuration;
using Common;
using Model;
namespace DAL
{
    public class CommonDAL
    {
        public static string conn = ConfigurationManager.AppSettings["ConnStr"];
        //增删改执行语句
        public static bool ExecuteNonQuery(string proc, SqlParameter[] para)
        {
            int result = SQLHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, proc, para);
            return result == 0 ? false : true;
        }
        public static bool ExecuteNoneQuery(string text, SqlParameter[] para)
        {
            int result = SQLHelper.ExecuteNonQuery(conn, CommandType.Text, text, para);
            return result == 0 ? false : true;
        }
        public static bool ExecuteNoneQuery(string text)
        {
            int result = SQLHelper.ExecuteNonQuery(conn, CommandType.Text, text, null);
            return result == 0 ? false : true;
        }
        public static SqlDataReader ExecuteReader(string proc, SqlParameter[] para)
        {
            return SQLHelper.ExecuteReader(conn, CommandType.StoredProcedure, proc, para);
        }
        public static object ExecuteScalar(string proc, SqlParameter[] para)
        {
            return SQLHelper.ExecuteScalar(conn, CommandType.StoredProcedure, proc, para);
        }
        public static object ExecuteScalar2(string text, SqlParameter[] para)
        {
            return SQLHelper.ExecuteScalar(conn, CommandType.Text, text, para);
        }
        public static object ExecuteScalar(string text)
        {
            return SQLHelper.ExecuteScalar(conn, CommandType.Text, text, null);
        }
        public static DataTable GetDataTable(string proc, SqlParameter[] para)
        {
            return SQLHelper.GetDataTable(conn, CommandType.StoredProcedure, proc, para);
        }
        public static DataTable GetDataTable(string text)
        {
            return SQLHelper.GetDataTable(conn, CommandType.Text, text, null);
        }
        public static DataRow GetDataRow(string proc, SqlParameter[] para)
        {
            return SQLHelper.GetDataRow(conn, CommandType.StoredProcedure, proc, para);
        }
        public static DataRow GetDataRow(string text)
        {
            return SQLHelper.GetDataRow(conn, CommandType.Text, text, null);
        }
        public static bool ExecuteTransaction(string flag,string[] proc, SqlParameter[][] para)
        {
            return SQLHelper.ExecuteTransaction(flag, conn, CommandType.StoredProcedure, proc, para);
        }
        public static void BatchInsert(DataTable dt)
        {
            SQLHelper.BatchInsert(conn, dt);
        }
        public static void BatchInsert2(DataTable dt)
        {
            SQLHelper.BatchInsert2(conn, dt);
        }
        public static bool DeleteByID(int id, string table)
        {
            SqlParameter[] sp = new SqlParameter[2];
            sp[0] = new SqlParameter("@id", id);
            sp[1] = new SqlParameter("@table", table);
            return CommonDAL.ExecuteNonQuery("delete_by_id", sp);
        }
        public static bool DeleteByIDs(string ids, string table)
        {
            if (!string.IsNullOrEmpty(ids))
            {
                SqlParameter[] sp = new SqlParameter[2];
                sp[0] = new SqlParameter("@ids", ids);
                sp[1] = new SqlParameter("@table", table);
                return CommonDAL.ExecuteNonQuery("delete_by_ids", sp);
            }
            else
            {
                return false;
            }
            
        }
       
        public static string FindColumnByColumn(string column, string wcolumn, string wcolumnvalue, string table)
        {
            SqlParameter[] sp = new SqlParameter[4];
            sp[0] = new SqlParameter("@column", column);
            sp[1] = new SqlParameter("@wcolumn", wcolumn);
            sp[2] = new SqlParameter("@wcolumnvalue", wcolumnvalue);
            sp[3] = new SqlParameter("@table", table);
            return DataConvert.ToString(CommonDAL.ExecuteScalar("findcolumn_by_column", sp));

        }
       
        public static DataTable FindListByColumns(string column, string columnvalue, string table)
        {
            SqlParameter[] sp = new SqlParameter[3];
            sp[0] = new SqlParameter("@column", column);
            sp[1] = new SqlParameter("@columnvalue", columnvalue);
            sp[2] = new SqlParameter("@table", table);
            return CommonDAL.GetDataTable("findlist_by_columns", sp);
        }

        public static DataRow FindOneByID(int id, string table)
        {
            SqlParameter[] sp = new SqlParameter[2];
            sp[0] = new SqlParameter("@id", id);
            sp[1] = new SqlParameter("@table", table);
            DataTable dt = CommonDAL.GetDataTable("findone_by_id", sp);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            else
            {
                return null;
            }
        }
        public static DataTable FindOneByIDs(int id, string table)
        {
            SqlParameter[] sp = new SqlParameter[2];
            sp[0] = new SqlParameter("@id", id);
            sp[1] = new SqlParameter("@table", table);
            return CommonDAL.GetDataTable("findone_by_id", sp);
        }
        public static string FindColumnByID(int id, string column, string table)
        {
            SqlParameter[] sp = new SqlParameter[3];
            sp[0] = new SqlParameter("@id", id);
            sp[1] = new SqlParameter("@column", column);
            sp[2] = new SqlParameter("@table", table);
            return DataConvert.ToString(CommonDAL.ExecuteScalar("findcolumn_by_id", sp));

        }
        public static DataTable FindListByIDs(string ids, string table)
        {

            if (!string.IsNullOrEmpty(DataConvert.ToString(ids).Trim().Trim(',')))
            {
                SqlParameter[] sp = new SqlParameter[2];
                sp[0] = new SqlParameter("@ids", ids.Trim(','));
                sp[1] = new SqlParameter("@table", table);
                return CommonDAL.GetDataTable("findlist_by_ids", sp);
            }
            else
            {
                return new DataTable();
            }

        }
        public static DataRow FindOneByColumn(int comid, string column, string columnvalue, string table)
        {
            SqlParameter[] sp = new SqlParameter[3];
            sp[0] = new SqlParameter("@column", column);
            sp[1] = new SqlParameter("@columnvalue", columnvalue);
            sp[2] = new SqlParameter("@table", table);
            DataTable dt = CommonDAL.GetDataTable("findlist_by_column", sp);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }
            else
            {
                return null;
            }
        }

        //单改
        public static bool UpdateColumnByID(int id, string column, object value, string table)
        {
            SqlParameter[] sp = new SqlParameter[4];
            sp[0] = new SqlParameter("@id", id);
            sp[1] = new SqlParameter("@column", column);
            sp[2] = new SqlParameter("@value", value);
            sp[3] = new SqlParameter("@table", table);
            return CommonDAL.ExecuteNonQuery("updatecolumn_by_id", sp);

        }

        //根据某列修改
        public static bool UpdateColumnByColumn(string column, object value, string wcolumn, string wvalue, string table)
        {
            SqlParameter[] sp = new SqlParameter[5];
            sp[0] = new SqlParameter("@column", column);
            sp[1] = new SqlParameter("@value", value);
            sp[2] = new SqlParameter("@wcolumn", wcolumn);
            sp[3] = new SqlParameter("@wvalue", wvalue);
            sp[4] = new SqlParameter("@table", table);
            return CommonDAL.ExecuteNonQuery("updatecolumn_by_column", sp);

        }

        public static DataTable FindPage(int startindex,int pagesize, string order,string desc, string filter,string table,out int count) {
            if (order == "")
            {
                order = "ID";
            }
            if (desc == "")
            {
                desc = "desc";
            }
            SqlParameter[] sp = new SqlParameter[7];
            sp[0] = new SqlParameter("@startindex", startindex);
            sp[1] = new SqlParameter("@pagesize", pagesize);
            sp[2] = new SqlParameter("@order", order);
            sp[3] = new SqlParameter("@desc", desc);
            sp[4] = new SqlParameter("@filter", filter);
            sp[5] = new SqlParameter("@table", table);
            sp[6] = new SqlParameter("@count",SqlDbType.Int,10);
            sp[6].Direction = ParameterDirection.Output;
            DataTable dt = GetDataTable("table_page", sp);
            count = DataConvert.ToInt(sp[6].Value);
            return dt;
        }
        public static DataTable FindPage(int startindex, int pagesize, string order, string desc, StringBuilder filter, string table, out int count)
        {
            if (order == "")
            {
                order = "ID";
            }
            if (desc == "")
            {
                desc = "desc";
            }
            SqlParameter[] sp = new SqlParameter[7];
            sp[0] = new SqlParameter("@startindex", startindex);
            sp[1] = new SqlParameter("@pagesize", pagesize);
            sp[2] = new SqlParameter("@order", order);
            sp[3] = new SqlParameter("@desc", desc);
            sp[4] = new SqlParameter("@filter", filter.ToString());
            sp[5] = new SqlParameter("@table", table);
            sp[6] = new SqlParameter("@count", SqlDbType.Int, 10);
            sp[6].Direction = ParameterDirection.Output;
            DataTable dt = GetDataTable("table_page", sp);
            count = DataConvert.ToInt(sp[6].Value);
            return dt;
        }
        public static DataTable FindCPage(int startindex, int pagesize, string order, string desc, string filter, string proc, out int count)
        {
            if (order == "")
            {
                order = "ID";
            }
            if (desc == "")
            {
                desc = "desc";
            }
            SqlParameter[] sp = new SqlParameter[6];
            sp[0] = new SqlParameter("@startindex", startindex);
            sp[1] = new SqlParameter("@pagesize", pagesize);
            sp[2] = new SqlParameter("@order", order);
            sp[3] = new SqlParameter("@desc", desc);
            sp[4] = new SqlParameter("@filter", filter);
            sp[5] = new SqlParameter("@count", SqlDbType.Int, 10);
            sp[5].Direction = ParameterDirection.Output;
            DataTable dt = CommonDAL.GetDataTable(proc, sp);
            count = DataConvert.ToInt(sp[5].Value);
            return dt;
        }
        public static DataTable FindTop(int top, string order, string desc, string filter, string table)
        {
            if (order == "")
            {
                order = "ID";
            }
            if (desc == "")
            {
                desc = "desc";
            }
            SqlParameter[] sp = new SqlParameter[5];
            sp[0] = new SqlParameter("@top", top);
            sp[1] = new SqlParameter("@order", order);
            sp[2] = new SqlParameter("@desc", desc);
            sp[3] = new SqlParameter("@filter", filter);
            sp[4] = new SqlParameter("@table", table);
            DataTable dt = GetDataTable("table_top", sp);
            return dt;
        }

        public static int FindCount(string filter, string table)
        {
            SqlParameter[] sp = new SqlParameter[2];
            sp[0] = new SqlParameter("@filter", filter);
            sp[1] = new SqlParameter("@table", table);
            return DataConvert.ToInt(ExecuteScalar("find_count", sp));
        }

        public static string GetNum(DateTime begindate, DateTime enddate, string table)
        {
            SqlParameter[] sp = new SqlParameter[3];
            sp[0] = new SqlParameter("@begindate", begindate);
            sp[1] = new SqlParameter("@enddate", enddate);
            sp[2] = new SqlParameter("@table", table);
            return DataConvert.ToString(CommonDAL.ExecuteScalar("findnum_by_date", sp));

        }
    }
}
